# -*- coding:utf-8 _*-  
""" 
@author:yanming
@file: database.py
@version:
@time: 2020/07/24 
@email:476588294@qq.com
@function： 
"""
import pymysql
import os
from pandas import read_excel
from sqlalchemy import create_engine
from configobj import ConfigObj
import src.config as conf


class DataBase(object):
    def __init__(self, header, table):
        config = ConfigObj('config.ini')
        self.host = config['Server']['ip']
        self.port = int(config['Server']['port'])
        self.database = config['Server']['database']
        self.start_row = int(config['excel']['start_row'])
        self.user = conf.db_user

        self.password = conf.db_password
        self.db_table = table
        self.coding = 'utf8'
        self.header = header
        self.use_cols = 'A:O'

        engine_words = "mysql+pymysql://{}:{}@{}:{}/{}".format(self.user, self.password, self.host, self.port,
                                                               self.database)
        self.engine = create_engine(engine_words)

    def get_connect(self):
        if not self.database:
            raise (NameError, "没有设置数据库信息")
        con = pymysql.connect(host=self.host, port=self.port, user=self.user,
                              password=self.password, database=self.database, charset=self.coding)
        cur = con.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return cur, con

    def get_wjm_list(self):
        cur, db = self.get_connect()
        cur.execute("SELECT wjmc FROM {}".format(self.db_table))
        data = cur.fetchall()
        cur.close()
        data = [i[0] for i in data]
        return list(set(data))

    def write_sql(self, file_path):
        try:
            header = list(self.header.keys())[:-1]
            # 获取excel数据，并存入数据库
            df = read_excel(file_path, header=self.start_row, usecols=self.use_cols, names=header,
                            keep_default_na=False, sheet_name='清单')
            basename = os.path.basename(file_path)
            df['wjmc'] = basename
            df.to_sql(self.db_table, self.engine, index=False, if_exists='append')
        except Exception as err:
            print(err, file_path)
            return err
